package cn.hls.until;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import cn.hls.entity.vo.IExcel;

/**
 *@fileName: ExcelUtils.java
 *@package: cn.christ.until
 *@Description:  excel数据操作辅助类
 *@author: yinghui.zhang
 *@email: uuhui@163.com
 *@date: 2015-9-21
 */
@SuppressWarnings("deprecation")
public class ExcelUtils {

	/**
	 * 创建简单的excel列表文件对象
	 * @param widths 字段宽度
	 * @param titles 字段中文
	 * @param fieldNames 字段英文
	 * @param sheetName sheet名
	 * @param data 列表数据
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public static Workbook getSimpleListWorkbook(Object[] widths,String[] fieldNames, Object[] titles,  String sheetName, List data) {
		Workbook book=new HSSFWorkbook();
		Sheet sheet=book.createSheet(sheetName);
		//设置列的宽度
		for(int i=0;i<widths.length;i++){
			sheet.setColumnWidth(i, Integer.valueOf(widths[i]+"").intValue()*50);
		}
		
		//设置header 样式
		CellStyle headStyle=book.createCellStyle();
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
		//设置head 边框
		headStyle.setBorderRight(CellStyle.BORDER_THIN);
		headStyle.setBorderTop(CellStyle.BORDER_THIN);
		headStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headStyle.setBorderBottom(CellStyle.BORDER_THIN);
		
		//设置head 字体
		Font headFont = book.createFont();
		headFont.setFontHeightInPoints((short) 20); // 字体大小
		headFont.setFontName("微软雅黑"); // 什么字体
		headFont.setItalic(false); // 是不倾斜
		headFont.setStrikeout(false); // 是不是划掉
		
		headStyle.setFont(headFont);
		
		// title 样式
		CellStyle titleStyle = book.createCellStyle();
		titleStyle.setBorderRight(CellStyle.BORDER_THIN);
		titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
		titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		Font titleFont = book.createFont();
		titleFont.setFontHeightInPoints((short) 12); // 字体大小
		titleFont.setFontName("微软雅黑"); // 什么字体
		titleFont.setItalic(false); // 是不倾斜
		titleFont.setStrikeout(false); // 是不是划掉
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗

		titleStyle.setFont(titleFont);
		
		// 数据 样式
		CellStyle dataStyle = book.createCellStyle();
		dataStyle.setBorderRight(CellStyle.BORDER_THIN);
		dataStyle.setBorderBottom(CellStyle.BORDER_THIN);
		dataStyle.setBorderLeft(CellStyle.BORDER_THIN);
		dataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		dataStyle.setAlignment(CellStyle.ALIGN_CENTER);
		
		Font dataFont = book.createFont();
		dataFont.setFontHeightInPoints((short) 11); // 字体大小
		dataFont.setFontName("微软雅黑"); // 什么字体
		dataFont.setItalic(false); // 是不倾斜
		dataFont.setStrikeout(false); // 是不是划掉
		
		dataStyle.setFont(dataFont);
		
		// 1 新建 Head
		Row head = sheet.createRow((short) 0);
		Cell headCell = head.createCell(0);
		head.setHeightInPoints(60);
		headCell.setCellStyle(headStyle);
		headCell.setCellValue(sheetName);
		for (int i = 1; i < titles.length; i++) {
			Cell c = head.createCell(i);
			c.setCellStyle(headStyle);
		}
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.length-1));// 合并单元格，参数：first row, last row, first column,
		
		// 2 新建表头
		Row title = sheet.createRow((short) 1);
		title.setHeightInPoints(30);
		for (int i = 0; i < titles.length; i++) {
			Cell cell = title.createCell(i);
			cell.setCellValue(titles[i]+"");
			cell.setCellStyle(titleStyle);
		}
		
		
		int datalength = data==null?0:data.size();
		// 3 填充数据
		for (int i = 0; i < datalength; i++) {
			Row row = sheet.createRow(i + 2);
			row.setHeightInPoints(20);
			IExcel iexcel = (IExcel)data.get(i);
			//行单元格注入
			for (int j = 0; j < titles.length; j++) {
				Cell cell = row.createCell(j);
				Object v = iexcel.getValueByFieldName(fieldNames[j]);
				cell.setCellValue(String.valueOf(v==null?"":v));
				cell.setCellStyle(dataStyle);
			}
		}
		return book;
	}
}
